﻿using System;
using System.Data.SQLite;
using System.Globalization;

using Vepa.Shared;
using log4net;

namespace Vepa.Server.Database
{
    public class SQLite : IDatabase
    {
        private SQLiteConnection con;
        private SQLiteCommand cmd;

        CultureInfo provider = CultureInfo.InvariantCulture;

        private static readonly ILog log = LogManager.GetLogger(typeof(SQLite));

        public SQLite()
        {
        }

        public void Open()
        {
            string DataSource = "db.sqlite";
            con = new SQLiteConnection();
            con.ConnectionString = "Data Source=" + DataSource;
            con.Open();

            using (cmd = new SQLiteCommand(con))
            {
                cmd.CommandText = string.Format("CREATE TABLE IF NOT EXISTS Vepas (id integer primary key, version INTERGER NOT NULL, release DATETIME, hash TEXT);");
                cmd.ExecuteNonQuery();
                log.Info("Created table \"Vepa\"");
            }
        }

        public void Close()
        {
            con.Close();
            con.Dispose();
        }



        public void createVepaInfo(VepaInfo vepa)
        {
            using (cmd = new SQLiteCommand(con))
            {
                cmd.CommandText = string.Format("INSERT INTO Vepas (version, release) VALUES('{0}','{1}')", vepa.Version, vepa.ReleaseDateTime);
                cmd.ExecuteNonQuery();
            }

            log.Info("Created new Vepa entry (Version: " + vepa.Version + ")");
        }

        public VepaInfo getVepaInfo(int version)
        {
            VepaInfo info = new VepaInfo();

            using (cmd = new SQLiteCommand(con))
            {                
                cmd.CommandText = string.Format("SELECT version, release FROM Vepas WHERE(version='{0}')", version);
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                if (reader.HasRows)
                {
                    info.Version = reader.GetInt32(0);
                    //info.ReleaseDateTime = DateTime.ParseExact(reader.GetString(1), "d/M/yyyy h:mm:ss tt", null); //Ohh crap. Let's just hope that this is going to work in every case.
                    info.ReleaseDateTime = DateTime.Parse(reader.GetString(1), provider, DateTimeStyles.AssumeUniversal);
                }
                else
                {
                    info = null;
                }
                reader.Close();
                reader.Dispose();
            }         
            return info;
        }

        public VepaInfo[] getAllVepaInfos()
        {
            System.Collections.Generic.List<VepaInfo> list = new System.Collections.Generic.List<VepaInfo>();

            using (cmd = new SQLiteCommand(con))
            {
                cmd.CommandText = "SELECT version, release FROM Vepas";
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    VepaInfo info = new VepaInfo();
                    info.Version = reader.GetInt32(0);
                    //info.ReleaseDateTime = DateTime.ParseExact(reader.GetString(1), "d/M/yyyy h:mm:ss tt", null); //Ohh crap. Let's just hope that this is going to work in every case.
                    info.ReleaseDateTime = DateTime.Parse(reader.GetString(1), provider, DateTimeStyles.AssumeUniversal);
                    list.Add(info);
                }              
               
                reader.Close();
                reader.Dispose();
            }
            return list.ToArray();
        }

        public VepaInfo getLatestVepaInfo()
        {
            VepaInfo info = new VepaInfo();

            using (cmd = new SQLiteCommand(con))
            {
                cmd.CommandText = string.Format("SELECT version, release FROM Vepas ORDER BY version DESC LIMIT 1");
                SQLiteDataReader reader = cmd.ExecuteReader();
                reader.Read();
                if (reader.HasRows)
                {
                    info.Version = reader.GetInt32(0);                   
                   
                    //info.ReleaseDateTime = DateTime.ParseExact(datetime, "d/M/yyyy h:mm:ss tt", provider); //Ohh crap. Let's just hope that this is going to work in every case.
                    info.ReleaseDateTime = DateTime.Parse(reader.GetString(1), provider, DateTimeStyles.AssumeUniversal);
                }
                else
                {
                    info = null;
                }
                reader.Close();
                reader.Dispose();
            }
            return info;

        }
        public void dropVepaInfo(int version)
        {
            throw new NotImplementedException();
            log.Info("Dropped Vepa version " + version);
        }
    }
}
